﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using System.Web;
using Entities;
using System.Web.UI.WebControls;

namespace Data
{
   public class DataAccess
    {
        public static void ExeStoredWithParamNon(string storedName, params object[] prms)
        {
            SqlConnection conn = Connect.GetConnect();
            SqlCommand cmd = new SqlCommand();
            try
            {
                cmd.CommandText = storedName;
                cmd.Connection = conn;
                conn.Open();
                cmd.CommandType = CommandType.StoredProcedure;
                for (int i = 0; i < prms.Length; i += 2)
                {
                    SqlParameter param = new SqlParameter(prms[i].ToString(), prms[i + 1]);
                    cmd.Parameters.Add(param);
                }
                cmd.ExecuteNonQuery();
                conn.Close();
            }
            catch { }
            finally
            {
                if (conn.State == ConnectionState.Open)
                    conn.Close();
                conn.Dispose();
            }
        }

        /**
         * Execute stored procedure non-return
         * */
        public static void ExeStoredNon(string storedName)
        {
            SqlCommand cmd = new SqlCommand();
            //cmd.CommandTimeout = 2000;
            SqlConnection conn = Connect.GetConnect();
            cmd.Parameters.Clear();
            cmd.CommandText = storedName;
            cmd.CommandType = CommandType.StoredProcedure;
            try
            {
                cmd.Connection = conn;
                conn.Open();
                cmd.ExecuteNonQuery();
                conn.Close();
            }
            catch
            {
                throw;
            }
            finally
            {
                if (conn.State == ConnectionState.Open)
                    conn.Close();
                conn.Dispose();
            }
        }

        /**
         * Execute stored procedure with parameter return DataTable
         * */
        public static DataTable ExeStoredWithParamToDataTable(string storedName, params object[] prms)
        {
            DataTable dt = new DataTable();
            SqlConnection conn = Connect.GetConnect();
            SqlCommand cmd = new SqlCommand();
            try
            {
                cmd.Connection = conn;
                conn.Open();
                cmd.CommandText = storedName;
                cmd.CommandType = CommandType.StoredProcedure;
                for (int i = 0; i < prms.Length; i += 2)
                {
                    SqlParameter pa = new SqlParameter(prms[i].ToString(), prms[i + 1]);
                    cmd.Parameters.Add(pa);
                }
                SqlDataAdapter da = new SqlDataAdapter();
                da.SelectCommand = cmd;
                da.Fill(dt);
                conn.Close();
                cmd.Dispose();
            }
            catch { }
            finally
            {
                if (conn.State == ConnectionState.Open)
                    conn.Close();
                conn.Dispose();
            }
            return dt;
        }

        /**
         * Execute stored procedure with parameter, return number of record
         * 
         * */
        public static int ExeStoredCount(string storedName, params object[] prms)
        {
            int count = 0;
            SqlConnection conn = Connect.GetConnect();
            SqlCommand cmd = new SqlCommand();
            try
            {
                cmd.Connection = conn;
                conn.Open();
                cmd.CommandText = storedName;
                cmd.CommandType = CommandType.StoredProcedure;
                for (int i = 0; i < prms.Length; i += 2)
                {
                    SqlParameter pa = new SqlParameter(prms[i].ToString(), prms[i + 1]);
                    cmd.Parameters.Add(pa);
                }
                count = (int)cmd.ExecuteScalar();
                conn.Close();
                cmd.Dispose();
            }
            catch { }
            finally
            {
                if (conn.State == ConnectionState.Open)
                    conn.Close();
                conn.Dispose();
            }
            return count;
        }

        /**
         * Execute stored procedure with parameter, return DropDownList
         * 
         * */
        public static void ExeStoredToDropDown(string StoredName, DropDownList drl, params object[] prms)
        {
            SqlConnection conn = Connect.GetConnect();
            SqlCommand cmd = new SqlCommand();
            SqlDataReader dr;
            try
            {
                cmd.Connection = conn;
                conn.Open();
                cmd.CommandText = StoredName;
                cmd.CommandType = CommandType.StoredProcedure;
                for (int i = 0; i < prms.Length; i += 2)
                {
                    SqlParameter pa = new SqlParameter(prms[i].ToString(), prms[i + 1]);
                    cmd.Parameters.Add(pa);
                }
                dr = cmd.ExecuteReader();
                while (dr.Read())
                {
                    drl.Items.Add(Convert.ToString(dr.GetString(0)));
                }
                dr.Close();
                conn.Close();
            }
            catch { }
            finally
            {
                if (conn.State == ConnectionState.Open)
                    conn.Close();
                conn.Dispose();
            }
        }

        /**
         * Execute stored procedure with param, return decimal
         * */
        public static decimal ExeStoredToDecimal(string storedName, params object[] para)
        {
            decimal data = 0;
            SqlConnection conn = Connect.GetConnect();
            SqlCommand cmd = new SqlCommand();
            SqlDataReader dr;
            try
            {
                cmd.Connection = conn;
                conn.Open();
                cmd.CommandText = storedName;
                cmd.CommandType = CommandType.StoredProcedure;
                for (int i = 0; i < para.Length; i += 2)
                {
                    SqlParameter pa = new SqlParameter(para[i].ToString(), para[i + 1]);
                    cmd.Parameters.Add(pa);
                }
                dr = cmd.ExecuteReader();
                while (dr.Read())
                {
                    data = Convert.ToDecimal(dr[0]);
                }
                dr.Close();
                conn.Close();
            }
            catch { }
            finally
            {
                if (conn.State == ConnectionState.Open)
                    conn.Close();
                conn.Dispose();
            }
            return data;
        }

        /**
         * Execute stored procedure with param, return double value
         * 
         * */
        public static double ExeStoredToDouble(string storedName, params object[] para)
        {
            double data = 0;
            SqlConnection conn = Connect.GetConnect();
            SqlCommand cmd = new SqlCommand();
            SqlDataReader dr;
            try
            {
                cmd.Connection = conn;
                conn.Open();
                cmd.CommandText = storedName;
                cmd.CommandType = CommandType.StoredProcedure;
                for (int i = 0; i < para.Length; i += 2)
                {
                    SqlParameter pa = new SqlParameter(para[i].ToString(), para[i + 1]);
                    cmd.Parameters.Add(pa);
                }
                dr = cmd.ExecuteReader();
                while (dr.Read())
                {
                    data = Convert.ToDouble(dr[0]);
                }
                dr.Close();
                conn.Close();
            }
            catch { }
            finally
            {
                if (conn.State == ConnectionState.Open)
                    conn.Close();
                conn.Dispose();
            }
            return data;
        }

        /**
         * Execute stored procedure with param, return double value
         * 
         * */
        public static int ExeStoredToInt(string storedName, params object[] para)
        {
            int data = 0;
            SqlConnection conn = Connect.GetConnect();
            SqlCommand cmd = new SqlCommand();
            SqlDataReader dr;
            try
            {
                cmd.Connection = conn;
                conn.Open();
                cmd.CommandText = storedName;
                cmd.CommandType = CommandType.StoredProcedure;
                for (int i = 0; i < para.Length; i += 2)
                {
                    SqlParameter pa = new SqlParameter(para[i].ToString(), para[i + 1]);
                    cmd.Parameters.Add(pa);
                }
                dr = cmd.ExecuteReader();
                while (dr.Read())
                {
                    data = Convert.ToInt16(dr[0]);
                }
                dr.Close();
                conn.Close();
            }
            catch { }
            finally
            {
                if (conn.State == ConnectionState.Open)
                    conn.Close();
                conn.Dispose();
            }
            return data;
        }
    }
}
